from sqlalchemy import create_engine, func
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker

from commons.entity.po.schedule import Schedule
from commons.entity.po.travel import Travel
from commons.mappers.base_mapper import BaseMapper

# 创建数据库引擎
db_url = "mysql+pymysql://root:123456@localhost/ctdb"
engine = create_engine(db_url, echo=True)

# 创建会话类
Session = sessionmaker(bind=engine)


class ScheduleMapper(BaseMapper):
    def __init__(self):
        super().__init__(Session)

    # def insert(self, schedule_bean):
    #     """
    #     插入一个新的调度记录。
    #     :param schedule_bean: Schedule 类的一个实例，包含需要插入的调度信息
    #     :return: 插入操作是否成功，插入的调度ID
    #     """
    #     try:
    #         with self.session_factory() as session:
    #             # 获取最大 travel_id
    #             max_travel_id = session.query(func.max(Travel.travel_id)).scalar()
    #
    #             # 创建要插入的 Schedule 对象
    #             schedule = Schedule(
    #                 location=schedule_bean["location"] if schedule_bean["location"] is not None else None,
    #                 task=schedule_bean["task"] if schedule_bean["task"] is not None else None,
    #                 schedule_detail=schedule_bean["schedule_detail"] if schedule_bean["schedule_detail"] is not None else None,
    #                 start_date=schedule_bean["start_date"] if schedule_bean["start_date"] is not None else None,
    #                 end_time=schedule_bean["end_time"] if schedule_bean["end_time"] is not None else None,
    #                 travel_id=max_travel_id  # 设置为 travel 表中的最大 travel_id
    #             )
    #
    #             # 添加并提交事务
    #             session.add(schedule)
    #             session.commit()
    #
    #             # 获取插入的 schedule ID
    #             inserted_schedule_id = schedule.schedule_id  # 假设 schedule_id 是主键，自动生成的字段
    #             return True, inserted_schedule_id
    #
    #     except SQLAlchemyError as e:
    #         print(f"Database error during insert operation: {str(e)}")
    #         return False, None

    def delete_by_schedule_id(self, schedule_id):
        """
        根据 schedule_id 删除行程记录
        :param schedule_id: str
        :return: True（删除成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                schedule = session.query(Schedule).filter_by(schedule_id=schedule_id).first()
                if schedule:
                    session.delete(schedule)
                    session.commit()  # 提交事务
                    return True
                return False  # 行程记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during delete operation: {str(e)}")
            return False

    def update_by_schedule_id(self, schedule_update, schedule_id):
        """
        根据 schedule_id 更新行程信息
        :param schedule_id: str
        :param schedule_update: Schedule 类实例或字典，包含需要更新的行程属性
        :return: True（更新成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                schedule = session.query(Schedule).filter_by(schedule_id=schedule_id).first()
                if schedule:
                    # 如果 schedule_update 是类实例，转换为字典
                    if schedule_update and hasattr(schedule_update, 'to_dict'):
                        schedule_update = schedule_update.to_dict()

                    # 使用新数据更新行程记录属性
                    for key, value in schedule_update.items():
                        if value is not None and hasattr(schedule, key):
                            setattr(schedule, key, value)

                    session.commit()  # 提交事务
                    return True
                return False  # 行程记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during update operation: {str(e)}")
            return False

    def select_by_schedule_id(self, schedule_id):
        """
        根据 schedule_id 从数据库获取 Schedule 对象
        :param schedule_id: str
        :return: Schedule 对象或 None
        """
        try:
            with self.session_factory() as session:
                schedule = session.query(Schedule).filter_by(schedule_id=schedule_id).first()
                if schedule:
                    return schedule
                return None
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None

    def select_by_travel_id(self, travel_id):
        """
        根据 travel_id 从数据库获取 Schedule 对象
        :param travel_id: str
        :return: Schedule 对象或 None
        """
        try:
            with self.session_factory() as session:
                schedules = session.query(Schedule).filter_by(travel_id=travel_id).all()
                if schedules:
                    return schedules
                return None
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None